﻿using System;
using System.Data;
using System.Data.Common;
using Actuals.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Actuals.Data
{
    public class DalManager : IDalManager
    {
        private Database _database;
        private DbCommand _dbCommand;
        private string _storedProcedureName;
        private string _sqlStringCommand;
        private DbTransaction _currentTransaction;

        public DalManager()
        {
        }

        public DalManager(Database database)
        {
            _database = database;
        }

        public Database Database
        {
            get
            {
                if (_database == null)
                    _database = DatabaseFactory.CreateDatabase();

                return _database;
            }
        }

        public DbCommand DbCommand
        {
            get
            {
                return _dbCommand;
            }
        }

        public string StoredProcedureName
        {
            get
            {
                return _storedProcedureName;
            }
            set
            {
                _storedProcedureName = value;
                _dbCommand = Database.GetStoredProcCommand(value);
            }
        }

        public string SqlStringCommand
        {
            get
            {
                return _sqlStringCommand;
            }
            set
            {
                _sqlStringCommand = value;
                _dbCommand = Database.GetSqlStringCommand(value);
            }
        }



        #region Encapsulation

        public DataSet LoadDataSet(string tableName)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, DbCommand, ds, tableName);
            return ds;
        }

        public DataSet LoadDataSet(string tableName, DbTransaction transaction)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, DbCommand, ds, tableName, transaction);
            return ds;
        }

        public DataSet LoadDataSet(string[] tableNames)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, DbCommand, ds, tableNames);
            return ds;
        }

        public DataSet LoadDataSet(string[] tableNames, DbTransaction transaction)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, DbCommand, ds, tableNames, transaction);
            return ds;
        }

        public DataSet LoadDataSet(string storedProcedureName, string[] tableNames, params object[] parameterValues)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, storedProcedureName, ds, tableNames, parameterValues);
            return ds;
        }

        public DataSet LoadDataSet(DbTransaction transaction, string storedProcedureName, string[] tableNames, params object[] parameterValues)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, transaction, storedProcedureName, ds, tableNames, parameterValues);
            return ds;
        }

        public DataSet LoadDataSet(CommandType commandType, string commandText, string[] tableNames)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, commandType, commandText, ds, tableNames);
            return ds;
        }

        public DataSet LoadDataSet(DbTransaction transaction, CommandType commandType, string commandText, string[] tableNames)
        {
            DataSet ds = new DataSet();
            Execute(Database.LoadDataSet, transaction, commandType, commandText, ds, tableNames);
            return ds;
        }


        public DataSet LoadDataSet(string storedProcedureName, DataSet dataSet, string[] tableNames,
                                params object[] parameterValues)
        {
            Execute(Database.LoadDataSet, storedProcedureName, dataSet, tableNames, parameterValues);
            return dataSet;
        }

        public DataSet LoadDataSet(DbTransaction transaction, string storedProcedureName, DataSet dataSet,
                                string[] tableNames, params object[] parameterValues)
        {
            Execute(Database.LoadDataSet, transaction, storedProcedureName, dataSet, tableNames, parameterValues);
            return dataSet;
        }

        public DataSet LoadDataSet(CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
        {
            Execute(Database.LoadDataSet, commandType, commandText, dataSet, tableNames);
            return dataSet;
        }

        public DataSet LoadDataSet(DbTransaction transaction, CommandType commandType, string commandText, DataSet dataSet,
                                string[] tableNames)
        {
            Execute(Database.LoadDataSet, transaction, commandType, commandText, dataSet, tableNames);
            return dataSet;
        }

        public DataSet ExecuteDataSet()
        {
            return Execute<DbCommand, DataSet>(Database.ExecuteDataSet, DbCommand);
        }

        public DataSet ExecuteDataSet(DbTransaction transaction)
        {
            return Execute<DbCommand, DbTransaction, DataSet>(Database.ExecuteDataSet, DbCommand, transaction);
        }

        public DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            return Execute<string, object[], DataSet>(Database.ExecuteDataSet, storedProcedureName, parameterValues);
        }

        public DataSet ExecuteDataSet(DbTransaction transaction, string storedProcedureName,
                                      params object[] parameterValues)
        {
            return
                Execute<DbTransaction, string, object[], DataSet>(Database.ExecuteDataSet, transaction,
                                                                  storedProcedureName, parameterValues);
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            return Execute<CommandType, string, DataSet>(Database.ExecuteDataSet, commandType, commandText);
        }

        public DataSet ExecuteDataSet(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return
                Execute<DbTransaction, CommandType, string, DataSet>(Database.ExecuteDataSet, transaction, commandType,
                                                                     commandText);
        }

        public object ExecuteScalar()
        {
            return Execute<DbCommand, object>(Database.ExecuteScalar, DbCommand);
        }

        public object ExecuteScalar(DbTransaction transaction)
        {
            return Execute<DbCommand, DbTransaction, object>(Database.ExecuteScalar, DbCommand, transaction);
        }

        public object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            return Execute<string, object[], object>(Database.ExecuteScalar, storedProcedureName, parameterValues);
        }

        public object ExecuteScalar(DbTransaction transaction, string storedProcedureName,
                                    params object[] parameterValues)
        {
            return
                Execute<DbTransaction, string, object[], object>(Database.ExecuteScalar, transaction,
                                                                 storedProcedureName, parameterValues);
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return Execute<CommandType, string, object>(Database.ExecuteScalar, commandType, commandText);
        }

        public object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return
                Execute<DbTransaction, CommandType, string, object>(Database.ExecuteScalar, transaction, commandType,
                                                                    commandText);
        }

        public int ExecuteNonQuery()
        {
            return Execute<DbCommand, int>(Database.ExecuteNonQuery, DbCommand);
        }

        public int ExecuteNonQuery(DbTransaction transaction)
        {
            return Execute<DbCommand, DbTransaction, int>(Database.ExecuteNonQuery, DbCommand, transaction);
        }

        public int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            return Execute<string, object[], int>(Database.ExecuteNonQuery, storedProcedureName, parameterValues);
        }

        public int ExecuteNonQuery(DbTransaction transaction, string storedProcedureName,
                                   params object[] parameterValues)
        {
            return
                Execute<DbTransaction, string, object[], int>(Database.ExecuteNonQuery, transaction, storedProcedureName,
                                                              parameterValues);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return Execute<CommandType, string, int>(Database.ExecuteNonQuery, commandType, commandText);
        }

        public int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return
                Execute<DbTransaction, CommandType, string, int>(Database.ExecuteNonQuery, transaction, commandType,
                                                                 commandText);
        }

        public IDataReader ExecuteReader()
        {
            return Execute<DbCommand, IDataReader>(Database.ExecuteReader, DbCommand);
        }

        public IDataReader ExecuteReader(DbTransaction transaction)
        {
            return Execute<DbCommand, DbTransaction, IDataReader>(Database.ExecuteReader, DbCommand, transaction);
        }

        public IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            return Execute<string, object[], IDataReader>(Database.ExecuteReader, storedProcedureName, parameterValues);
        }

        public IDataReader ExecuteReader(DbTransaction transaction, string storedProcedureName,
                                         params object[] parameterValues)
        {
            return
                Execute<DbTransaction, string, object[], IDataReader>(Database.ExecuteReader, transaction,
                                                                      storedProcedureName, parameterValues);
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return Execute<CommandType, string, IDataReader>(Database.ExecuteReader, commandType, commandText);
        }

        public IDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText)
        {
            return
                Execute<DbTransaction, CommandType, string, IDataReader>(Database.ExecuteReader, transaction,
                                                                         commandType, commandText);
        }

        public void AddInParameter(string name, DbType dbType, string sourceColumn, DataRowVersion sourceVersion)
        {
            Database.AddInParameter(DbCommand, name, dbType, sourceColumn, sourceVersion);
        }

        public void AddInParameter(string name, DbType dbType, object value)
        {
            Database.AddInParameter(DbCommand, name, dbType, value);

        }

        public void AddInParameter(string name, DbType dbType)
        {
            Database.AddInParameter(DbCommand, name, dbType);
        }

        public void AddOutParameter(string name, DbType dbType, int size)
        {
            Database.AddOutParameter(DbCommand, name, dbType, size);
        }

        public void AddParameter(string name, DbType dbType, ParameterDirection direction,
                                 string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            Database.AddParameter(DbCommand, name, dbType, direction, sourceColumn, sourceVersion, value);
        }

        public void AddParameter(string name, DbType dbType, int size, ParameterDirection direction,
                                 bool nullable, byte precision, byte scale, string sourceColumn,
                                 DataRowVersion sourceVersion, object value)
        {
            Database.AddParameter(DbCommand, name, dbType, size, direction, nullable, precision, scale, sourceColumn,
                                  sourceVersion, value);
        }

        public object GetParameterValue(string name)
        {
            return Database.GetParameterValue(DbCommand, name);
        }

        public T GetValue<T>(DataRow row, string columnName)
        {

            if (row == null || string.IsNullOrEmpty(columnName) || !row.Table.Columns.Contains(columnName))

                return default(T);



            object obj = row[columnName];

            if (obj == null || obj is DBNull)

                return default(T);

            Type t = typeof(T);
            return t.IsGenericType && typeof(T).GetGenericTypeDefinition() == typeof(Nullable<>)
                       ? (T)Convert.ChangeType(obj, Nullable.GetUnderlyingType(t))
                       : (T)Convert.ChangeType(obj, t);
        }

        public void BeginTransaction()
        {
            if (_currentTransaction == null)
            {
                if (_dbCommand.Connection == null)
                {
                    _dbCommand.Connection = _database.CreateConnection();
                    _dbCommand.Connection.Open();
                }
            }
            _currentTransaction = _dbCommand.Connection.BeginTransaction();
            LogHelper.WriteInfo("Begin transaction");
        }

        public void CommitTransaction()
        {
            if (_currentTransaction != null)
            {
                _currentTransaction.Commit();
                LogHelper.WriteInfo("Commit transaction");
            }
            else
            {
                throw new ApplicationException("No current transaction defined");
            }
        }

        public void RollbackTransaction()
        {
            if (_currentTransaction != null)
            {
                _currentTransaction.Rollback();
                LogHelper.WriteSqlInfo(_dbCommand, "Rollbacking current transaction");
            }
            else
            {
                throw new ApplicationException("No current transaction defined");
            }
        }

        #endregion

        #region Execution

        private void Execute<T1, T2, T3>(Void<T1, T2, T3> action, T1 type1, T2 type2, T3 type3)
        {
            try
            {
                action(type1, type2, type3);
                LogHelper.WriteSqlDebug(_dbCommand);
            }
            catch (Exception myException)
            {
                LogHelper.WriteError(myException);
                throw;
            }
            finally
            {
                if (_dbCommand != null)
                    _dbCommand.Dispose();
                LogHelper.WriteMethodExiting();
            }
        }

        private void Execute<T1, T2, T3, T4>(Void<T1, T2, T3, T4> action, T1 type1, T2 type2, T3 type3, T4 type4)
        {
            try
            {
                action(type1, type2, type3, type4);
                LogHelper.WriteSqlDebug(_dbCommand);
            }
            catch (Exception myException)
            {
                LogHelper.WriteError(myException);
                throw;
            }
            finally
            {
                if (_dbCommand != null)
                    _dbCommand.Dispose();
                LogHelper.WriteMethodExiting();
            }
        }

        private void Execute<T1, T2, T3, T4, T5>(Void<T1, T2, T3, T4, T5> action, T1 type1, T2 type2, T3 type3,
                                                        T4 type4, T5 type5)
        {
            try
            {
                action(type1, type2, type3, type4, type5);
                LogHelper.WriteSqlDebug(_dbCommand);
            }
            catch (Exception myException)
            {
                LogHelper.WriteSqlError(_dbCommand, myException);
                throw;
            }
            finally
            {
                if (_dbCommand != null)
                    _dbCommand.Dispose();
                LogHelper.WriteMethodExiting();
            }
        }

        private TResult Execute<T1, TResult>(Actuals.Common.Func<T1, TResult> func, T1 type1)
        {
            TResult result;

            try
            {
                result = func(type1);
                LogHelper.WriteSqlDebug(_dbCommand);
            }
            catch (Exception myException)
            {
                LogHelper.WriteSqlError(_dbCommand, myException);
                throw;
            }
            finally
            {
                if (_dbCommand != null)
                    _dbCommand.Dispose();
                LogHelper.WriteMethodExiting();
            }

            return result;
        }

        private TResult Execute<T1, T2, TResult>(Actuals.Common.Func<T1, T2, TResult> func, T1 type1, T2 type2)
        {
            TResult result;

            try
            {
                result = func(type1, type2);
                LogHelper.WriteSqlDebug(_dbCommand);
            }
            catch (Exception myException)
            {
                LogHelper.WriteSqlError(_dbCommand, myException);
                throw;
            }
            finally
            {
                if (_dbCommand != null)
                    _dbCommand.Dispose();
                LogHelper.WriteMethodExiting();
            }

            return result;
        }

        private TResult Execute<T1, T2, T3, TResult>(Actuals.Common.Func<T1, T2, T3, TResult> func, T1 type1, T2 type2, T3 type3)
        {
            TResult result;

            try
            {
                result = func(type1, type2, type3);
                LogHelper.WriteSqlDebug(_dbCommand);
            }
            catch (Exception myException)
            {
                LogHelper.WriteSqlError(_dbCommand, myException);
                throw;
            }
            finally
            {
                if (_dbCommand != null)
                    _dbCommand.Dispose();
                LogHelper.WriteMethodExiting();
            }

            return result;
        }

        #endregion
    }
}
